package ytr.android.sparrow;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;


/**
 * <pre>
 * データベース操作クラス。
 * @author toki
 * </pre>
 */
public class SparrowDBHelpter extends SQLiteOpenHelper {

	/**
	 * DataBaseファイル名称
	 */
	private static final String DBNAME = "SparrowDB";

	/**
	 * DataBaseバージョン情報
	 */
	private static final int DBVERSION = 1;

	/**
	 * 参加ユーザ名テーブル作成SQL
	 */
	private static final String CREATE_USER_NAME = "CREATE TABLE user_name (" +
	" user_id INTEGER PRIMARY KEY AUTOINCREMENT," + 
	" user_name TEXT NOT NULL" + 
	");";

	/**
	 * 参加ユーザ名テーブル初期データ追加SQL
	 */
	private static final String[] INSERT_DEFAULT_USER_NAME = {
		"INSERT INTO user_name(user_name) VALUES('User1');",
		"INSERT INTO user_name(user_name) VALUES('User2');",
		"INSERT INTO user_name(user_name) VALUES('User3');",
		"INSERT INTO user_name(user_name) VALUES('User4');",
		"INSERT INTO user_name(user_name) VALUES('User5');",
		"INSERT INTO user_name(user_name) VALUES('User6');",		
	};

	/**
	 * 参加ユーザ名テーブル検索SQL
	 */
	private static final String SELECT_USER_NAME = "SELECT * FROM user_name;";

	/**
	 * 参加ユーザ名テーブル更新SQL
	 */
	private static final String UPDATE_USER_NAME_FORMAT = "UPDATE user_name SET user_name = '%s' WHERE user_id == %d;";

	/**
	 * ユーザアイコンリソースID情報格納用オブジェクト
	 */
	private static final int[] userIconResourceIDArray = {
		R.drawable.user1_icon,
		R.drawable.user2_icon,
		R.drawable.user3_icon,
		R.drawable.user4_icon,
		R.drawable.user5_icon,
		R.drawable.user6_icon
	};

	/**
	 * コンストラクタ
	 * @param context
	 */
	public SparrowDBHelpter(Context context) {
		super(context, DBNAME, null, DBVERSION);
	}

	/**
	 * 
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(CREATE_USER_NAME);
		for (int i=0 ; i < INSERT_DEFAULT_USER_NAME.length ; i++) {
			db.execSQL(INSERT_DEFAULT_USER_NAME[i]);
		}
	}

	/**
	 * 
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		; // no operation
	}

	/**
	 * DBから参加ユーザ名テーブルデータを取得する
	 * @return
	 */
	public List<UserSettingItem> getUserNameTableData() {

		List<UserSettingItem> ret = new ArrayList<UserSettingItem>();

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(SELECT_USER_NAME, null);
		if (INSERT_DEFAULT_USER_NAME.length != cursor.getCount()) {
			Log.w(getClass().getName(), "unmatch record num");
		}

		boolean isEof = cursor.moveToFirst();
		int userNameColIdx = cursor.getColumnIndex("user_name");
		int userIDColIdx = cursor.getColumnIndex("user_id");
		for (int i=0 ; isEof ; i++) {
			UserSettingItem tmp = new UserSettingItem(userIconResourceIDArray[i], cursor.getString(userNameColIdx), cursor.getInt(userIDColIdx));
			ret.add(tmp);
			isEof = cursor.moveToNext();
		}
		cursor.close();
		db.close();

		return ret;
	}

	/**
	 * 参加ユーザ名テーブル更新処理
	 */
	public boolean setUserName(List<UserSettingItem> userNameList) {

		SQLiteDatabase db = this.getWritableDatabase();
		for (int i=0 ; i < userNameList.size() ; i++) {
			String insertSQL = String.format(UPDATE_USER_NAME_FORMAT, userNameList.get(i).getName(), userNameList.get(i).getDataBaseID());
			db.execSQL(insertSQL);
		}
		db.close();

		return true;
	}

}
